﻿'========================================================================
'功能:代理人資料庫存取物件
'作者:andy
'日期: 2011/8/29
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data


Namespace WorkFlow.DAO
    Public Class WFAgentDAO
        Inherits OAMS.DAO.BaseDAO

        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub




        ''' <summary>
        ''' 取得今日可以代理的人的資料
        ''' </summary>
        ''' <param name="Agent"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function GetAgentUID(ByVal Agent As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@Agent", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = Agent
            sqlParams(1) = New SqlParameter("@DD", SqlDbType.DateTime)
            sqlParams(1).Value = Today


            Dim sb As New StringBuilder()

            sb.AppendLine(" select B.emp_name, A.*  ")
            sb.AppendLine(" from  WF_Agent A  ")
            sb.AppendLine(" left join  Users B on A.UID=B.emp_no  ")
            sb.AppendLine(" where Agent=@Agent and StartDate<=@DD and EndDate>=@DD  and A.Status='Y'")


            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function


        ''' <summary>
        ''' 要修改代理人資料PS:自己才能修改自己的資料
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function GetData(ByVal UID As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(0) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID

            Dim sb As New StringBuilder()

            sb.AppendLine(" select B.emp_name as UIDName ,C.emp_name as AgentName,D.emp_name as CrtName,E.emp_name as ModiName, A.*  ")
            sb.AppendLine(" from  WF_Agent A  ")
            sb.AppendLine(" left join Users B on A.UID=B.emp_no  ")
            sb.AppendLine("  left join Users C on A.Agent=C.emp_no  ")
            sb.AppendLine(" left join Users D on A.CrtUID=D.emp_no ")
            sb.AppendLine(" left join Users E on A.ModiUID=E.emp_no  ")
            sb.AppendLine(" where UID=@UID ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function



        Public Sub InsertData(ByVal UID As String, ByVal Agent As String, ByVal StartDate As DateTime, ByVal EndDate As DateTime)
            Dim sqlParams As SqlParameter() = New SqlParameter(3) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID
            sqlParams(1) = New SqlParameter("@Agent", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = Agent
            sqlParams(2) = New SqlParameter("@StartDate", SqlDbType.DateTime)
            sqlParams(2).Value = StartDate
            sqlParams(3) = New SqlParameter("@EndDate", SqlDbType.DateTime)
            sqlParams(3).Value = EndDate

            Dim sb As New StringBuilder()

            sb.AppendLine(" declare @max1 int select @max1=max(SeqNo+1)  from  WF_Agent   where UID=@UID")
            sb.AppendLine(" set  @max1=isNull( @max1,1)  insert into WF_Agent (UID,SeqNo,Agent,StartDate,EndDate,Status,CrtUID,CrtDate,ModiUID) values (@UID,@max1,@Agent,@StartDate,@EndDate,'Y',@UID,GetDate(),'')")
           
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub

        Public Sub UpdateData(ByVal UID As String, ByVal SeqNo As Integer, ByVal Agent As String, ByVal StartDate As DateTime, ByVal EndDate As DateTime, ByVal Status As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(5) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID
            sqlParams(1) = New SqlParameter("@Agent", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = Agent
            sqlParams(2) = New SqlParameter("@StartDate", SqlDbType.DateTime)
            sqlParams(2).Value = StartDate
            sqlParams(3) = New SqlParameter("@EndDate", SqlDbType.DateTime)
            sqlParams(3).Value = EndDate
            sqlParams(4) = New SqlParameter("@SeqNo", SqlDbType.Int)
            sqlParams(4).Value = SeqNo
            sqlParams(5) = New SqlParameter("@Status", SqlDbType.NVarChar)
            sqlParams(5).Value = Status
            Dim sb As New StringBuilder()

            sb.AppendLine(" Update WF_Agent set ModiUID =@UID,ModiDate=GetDate(),Agent=@Agent,StartDate=@StartDate,EndDate=@EndDate,Status=@Status")
            sb.AppendLine(" where UID=@UID and SeqNo=@SeqNo")

            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub


    End Class


End Namespace


